"""
Case Type   : Timescaledb功能测试
Case Name   : 手动删除chunk成功
Create At   : 2025/05/15
Owner       : lihongji
Description :
              1.创建时序表
              2.查看时序表的所有chunk
              3.通过newer_than参数筛选时序表的chunk
              4.查看chunk的约束
              5.手动删除chunk
              6.清理环境
Expect      :
              1.创建时序表成功
              2.显示时序表的所有chunk
              3.筛选时序表chunk成功
              4.chunk约束存在
              5.删除chunk成功
              6.清理成功
History     :
"""

import unittest
import os

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node


class Timescaledb(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.constant = Constant()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.Constant = Constant()
        self.primary_sh = CommonSH('PrimaryDbPGmode')
        self.db_user_node = Node(node='PrimaryDbPGmode')
        self.t_name_1 = 'timescaledb_chunkutils_case0010'
        status = self.primary_sh.get_db_cluster_status()
        self.assertTrue("Normal" in status or "Degraded" in status)
        select_extension_cmd = f"SELECT * FROM pg_extension;"
        select_extension_result = self.primary_sh.execut_db_sql(
            select_extension_cmd)
        if "timescaledb" not in select_extension_result:
            create_extension_cmd = f"create extension timescaledb;"
            create_extension_result = (
                self.primary_sh.execut_db_sql(create_extension_cmd))
            self.assertIn(self.constant.extension_create_success,
                          create_extension_result, '创建timescaledb扩展失败')

    def test_add_dimension(self):
        text = '----step1:创建时序表 expect:创建成功----'
        self.log.info(text)
        create_table = (f"DROP TABLE IF EXISTS PUBLIC.{self.t_name_1};"
                        f"CREATE TABLE PUBLIC.{self.t_name_1}("
                        "time bigint, temp float8, device_id text);"
                        f"SELECT create_hypertable('public.{self.t_name_1}', "
                        "'time', chunk_time_interval => 1, "
                        "create_default_indexes=>FALSE);"
                        f"INSERT INTO PUBLIC.{self.t_name_1} "
                        "VALUES(6, 6.0, 'dev7'),(5, 5.0, 'dev7'),"
                        "(4, 4.0, 'dev7'),(3, 3.0, 'dev1');")
        create_table_result = self.primary_sh.execut_db_sql(create_table)
        self.log.info(create_table)
        self.log.info(create_table_result)
        self.assertIn(self.t_name_1, create_table_result, '执行失败' + text)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, create_table_result,
                      '执行失败' + text)

        text = '----step2:查看时序表的所有chunk expect:显示时序表的所有chunk----'
        self.log.info(text)
        show_chunks = f"SELECT show_chunks('{self.t_name_1}');"
        show_chunks_result = self.primary_sh.execut_db_sql(show_chunks)
        self.log.info(show_chunks)
        self.log.info(show_chunks_result)
        self.assertIn('(4 rows)', show_chunks_result, '执行失败' + text)

        text = ('---step3:通过newer_than参数筛选时序表的chunk '
                'expect:筛选时序表chunk成功---')
        self.log.info(text)
        show_chunks = "SELECT show_chunks(newer_than => 6);"
        show_chunks_result = self.primary_sh.execut_db_sql(show_chunks)
        self.log.info(show_chunks)
        self.log.info(show_chunks_result)
        self.assertIn('(1 row)', show_chunks_result, '执行失败' + text)

        text = '---step4:查看chunk的约束 expect:chunk约束存在---'
        self.log.info(text)
        check_chunk = """SELECT c.table_name, cc.constraint_name, ds.id AS 
        dimension_slice_id, ds.range_start, ds.range_end
        FROM _timescaledb_catalog.chunk c 
        INNER JOIN _timescaledb_catalog.chunk_constraint cc 
        ON (c.id = cc.chunk_id) 
        FULL OUTER JOIN 
        _timescaledb_catalog.dimension_slice ds 
        ON (ds.id = cc.dimension_slice_id) ORDER BY c.id;"""
        check_chunk_result = self.primary_sh.execut_db_sql(check_chunk)
        self.log.info(check_chunk)
        self.log.info(check_chunk_result)
        self.assertIn('constraint', check_chunk_result, '执行失败' + text)

        text = '---step5:手动删除chunk expect:删除chunk成功---'
        self.log.info(text)
        get_chunk_name = show_chunks_result.split()[2]
        drop_chunk = f"DROP TABLE {get_chunk_name};"
        drop_chunk_result = self.primary_sh.execut_db_sql(drop_chunk)
        self.log.info(check_chunk)
        self.log.info(check_chunk_result)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, drop_chunk_result,
                      '执行失败' + text)

    def tearDown(self):
        text = '-----step6:清理环境 expect:成功-----'
        self.log.info(text)
        drop_table = f"drop table PUBLIC.{self.t_name_1}"
        drop_table_result = self.primary_sh.execut_db_sql(drop_table)
        self.assertEqual(self.constant.DROP_TABLE_SUCCESS, drop_table_result,
                         '执行失败' + text)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')